using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


using Material.Object;
using Material.DAL;
namespace Material.Entity
{
	public class TblTransPenjBarangDTLEntity
	{
		private Accessor accessor;
		private string connString;

		public TblTransPenjBarangDTLEntity (string _connString)
		{
			 this.connString = _connString;
		}

		public int Insert(TblTransPenjBarangDTL tbltranspenjbarangdtl)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("INSERT INTO [TblTransPenjBarangDTL]");
            sql.AppendLine("(");
            sql.AppendLine("[TglJual]");
            sql.AppendLine(",[idHDR]");
            sql.AppendLine(",[IdBarang]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[HargaJual]");
            sql.AppendLine(",[Diskon]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(")");
            sql.AppendLine("VALUES");
            sql.AppendLine("(");
            sql.AppendLine("@TglJual");
            sql.AppendLine(",@idHDR");
            sql.AppendLine(",@IdBarang");
            sql.AppendLine(",@Jumlah");
            sql.AppendLine(",@HargaJual");
            sql.AppendLine(",@Diskon");
            sql.AppendLine(",@TotalHarga");
            sql.AppendLine(",@CreatedDate");
            sql.AppendLine(",@CreatedBy");
            sql.AppendLine(")");

			 int result = -1;
			 accessor = new Accessor(true, connString);
             accessor.CommandText = sql.ToString();
			 accessor.CommandType = CommandType.Text;
             accessor.Add("@TglJual", tbltranspenjbarangdtl.TglJual);
             accessor.Add("@idHDR", tbltranspenjbarangdtl.IdTransHDR);
			 accessor.Add("@IdBarang" ,tbltranspenjbarangdtl.IdBarang );
			 accessor.Add("@Jumlah" ,tbltranspenjbarangdtl.Jumlah );
             accessor.Add("@Diskon", tbltranspenjbarangdtl.Diskon);
			 accessor.Add("@TotalHarga" ,tbltranspenjbarangdtl.TotalHarga );
             accessor.Add("@HargaJual", tbltranspenjbarangdtl.HargaJual);
			 accessor.Add("@CreatedDate" ,tbltranspenjbarangdtl.CreatedDate );
			 accessor.Add("@CreatedBy" ,tbltranspenjbarangdtl.CreatedBy );


			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

		public int Update(TblTransPenjBarangDTL tbltranspenjbarangdtl)
		{
			 int result = -1;
			 accessor = new Accessor(true, connString);
			 accessor.CommandText = "TblTransPenjBarangDTL_Update";
			 accessor.CommandType = CommandType.StoredProcedure;
			 accessor.Add(@"IdTransDTL" ,tbltranspenjbarangdtl.IdTransDTL );
			 accessor.Add(@"IdTransHDR" ,tbltranspenjbarangdtl.IdTransHDR );
			 accessor.Add(@"IdBarang" ,tbltranspenjbarangdtl.IdBarang );
			 accessor.Add(@"Jumlah" ,tbltranspenjbarangdtl.Jumlah );
			 accessor.Add(@"HargaJual" ,tbltranspenjbarangdtl.HargaJual );
			 accessor.Add(@"TotalHarga" ,tbltranspenjbarangdtl.TotalHarga );
			// accessor.Add(@"CreatedDate" ,tbltranspenjbarangdtl.CreatedDate );
			 //accessor.Add(@"CreatedBy" ,tbltranspenjbarangdtl.CreatedBy );
			 accessor.Add(@"ModifiedDate" ,tbltranspenjbarangdtl.ModifiedDate );
			 accessor.Add(@"ModifiedBy" ,tbltranspenjbarangdtl.ModifiedBy );

			 result = Excutor.ExecuteNonQuery(accessor);
			 if (result != -1)
				 accessor.CommitTransaction();
			 else
				 accessor.RollBackTransaction();
			 return result;
		}

        public int Delete(int IdTransDTL)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("DELETE FROM [TblTransPenjBarangDTL]");
            sql.AppendLine(" WHERE IdTransDTL = @IdTransDTL");

            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;  
            accessor.Add(@"IdTransDTL", IdTransDTL);

            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
            accessor.CommitTransaction();
            else
            accessor.RollBackTransaction();
            return result;
		}

        public int DeleteByIdTransHDR(int IdHDR)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("DELETE FROM TblTransPenjBarangDTL WHERE IdHDR=@IdHDR");
           
            int result = -1;
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add("@IdHDR", IdHDR);


            result = Excutor.ExecuteNonQuery(accessor);
            if (result != -1)
                accessor.CommitTransaction();
            else
                accessor.RollBackTransaction();
            return result;
        }

		public TblTransPenjBarangDTL GetDataByID(int IdTransDTL)
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [IdTransDTL]");
            sql.AppendLine(",[TglJual]");
            sql.AppendLine(",[idHDR]");
            sql.AppendLine(",[IdBarang]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[HargaJual]");
            sql.AppendLine(",[Diskon]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine("FROM [TblTransPenjBarangDTL]");
            sql.AppendLine(" WHERE IdTransDTL = @IdTransDTL");

            TblTransPenjBarangDTL result = new TblTransPenjBarangDTL();
            accessor = new Accessor(true, connString);
            accessor.CommandText = sql.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"IdTransDTL" ,IdTransDTL);



			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 result.IdTransDTL = rdr.GetInt32(0);
                         result.TglJual = rdr.GetDateTime(1);
						 result.IdTransHDR = rdr.GetInt32(2);
						 result.IdBarang = rdr.GetInt32(3);
                         result.Jumlah = rdr.GetInt32(4);
                         result.HargaJual = rdr.GetDecimal(5);
                         result.Diskon = rdr.GetInt32(6);
                         result.TotalHarga = rdr.GetDecimal(7);
						 result.CreatedDate = rdr.GetDateTime(8);
						 result.CreatedBy = rdr.GetString(9);
                         if (rdr[10] != DBNull.Value)
						 result.ModifiedDate = rdr.GetDateTime(10);
                         if(rdr[11]!=DBNull.Value)
						 result.ModifiedBy = rdr.GetString(11);
					  }
			}
			 return result;
		}

        public DataSet GetDataBTglJual(string TglJual)
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine("       Select 		A.IdTransDTL	AS	'IdTransDTL',");
            s.AppendLine("       A.IdTransHDR	AS	'IdTransHDR',");
            s.AppendLine("       A.IdBarang	AS	'IdBarang',");
            s.AppendLine("       Convert(Varchar,A.Jumlah) +' ('+D.Satuan+' )'	AS	'Jumlah',");
            s.AppendLine("       A.HargaJual	AS	'HargaJual',");
            s.AppendLine("       A.TotalHarga	AS	'TotalHarga',");
            s.AppendLine("       A.CreatedDate	AS	'CreatedDate',");
            s.AppendLine("       A.CreatedBy	AS	'CreatedBy',");
            s.AppendLine("       A.ModifiedDate	AS	'ModifiedDate',");
            s.AppendLine("       A.ModifiedBy	AS	'ModifiedBy',");
            s.AppendLine("       C.NamaBarang,");
            s.AppendLine("       B.TglJual");

            s.AppendLine("From	TblTransPenjBarangDTL AS A INNER JOIN TblTransPenjBarangHDR B");
            s.AppendLine("ON A.IdTransHDR = B.IdTransHDR INNER JOIN TblBarang AS C");
            s.AppendLine("ON C.Id = A.IdBarang");
            s.AppendLine("INNER JOIN TblSatuan AS D ON C.IdSatuan = D.Id");
            s.AppendLine("WHERE CONVERT(VARCHAR,B.TglJual,103) = '" + TglJual +"'");


            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            accessor.Add(@"TglJual", TglJual);

             result = Excutor.GetDataSet(accessor);
           
            return result;
        }


        public DataSet GetDataByIdTransHDR(int IdHDR)
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine("Select A.IdTransDTL	AS	'IdTransDTL',");
            s.AppendLine("A.idHDR	AS	'IdTransHDR',");
            s.AppendLine("A.IdBarang	AS	'IdTransPembDTL',");
            s.AppendLine("Convert(Varchar,A.Jumlah) +' ('+F.Satuan+' )'	AS	'Jumlah',");
            s.AppendLine("A.HargaJual	AS	'HargaJual',");
            s.AppendLine("A.TotalHarga	AS	'TotalHarga',");
            s.AppendLine("A.CreatedDate	AS	'CreatedDate',");
            s.AppendLine("A.CreatedBy	AS	'CreatedBy',");
            s.AppendLine("A.ModifiedDate	AS	'ModifiedDate',");
            s.AppendLine("A.ModifiedBy	AS	'ModifiedBy',");
            s.AppendLine("D.NamaBarang,");
            s.AppendLine("CONVERT(VARCHAR,A.Diskon)+'%' AS Diskon,");
            s.AppendLine("CONVERT(VARCHAR(20),A.TglJual,113) AS TglJual"); //RCHAR(20), GETDATE(), 100)
            s.AppendLine("  From	TblTransPenjBarangDTL AS A INNER JOIN TblTransPenjBarangHDR B  ON A.idHDR = B.idHDR ");
            s.AppendLine(" INNER JOIN TblBarang D ON  A.IdBarang = D.Id ");
            s.AppendLine(" INNER JOIN TblSatuan AS F ON D.IdSatuan = F.Id");
           
            s.AppendLine("WHERE B.idHDR = " + IdHDR + "");


            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            

            result = Excutor.GetDataSet(accessor);

            return result;
        }




		public List<TblTransPenjBarangDTL> GetDataAll()
		{
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("SELECT [IdTransDTL]");
            sql.AppendLine(",[TglJual]");
            sql.AppendLine(",[idHDR]");
            sql.AppendLine(",[IdBarang]");
            sql.AppendLine(",[Jumlah]");
            sql.AppendLine(",[HargaJual]");
            sql.AppendLine(",[Diskon]");
            sql.AppendLine(",[TotalHarga]");
            sql.AppendLine(",[CreatedDate]");
            sql.AppendLine(",[CreatedBy]");
            sql.AppendLine(",[ModifiedDate]");
            sql.AppendLine(",[ModifiedBy]");
            sql.AppendLine(" FROM [TblTransPenjBarangDTL]");


			 List<TblTransPenjBarangDTL> result = new List<TblTransPenjBarangDTL>();
			 accessor = new Accessor(true, connString);
			 accessor.CommandText =sql.ToString();
			 accessor.CommandType = CommandType.Text;

			 SqlDataReader rdr = Excutor.GetDataReader(accessor);
			 if (rdr.HasRows)
			 {
				 while (rdr.Read())
					 {
						 TblTransPenjBarangDTL obj = new TblTransPenjBarangDTL();
						 obj.IdTransDTL = rdr.GetInt32(0);
                         obj.IdTransHDR = rdr.GetInt32(1);
						 obj.IdBarang = rdr.GetInt32(2);
						 obj.Jumlah = rdr.GetInt32(3);
						 obj.HargaJual = rdr.GetDecimal(4);
						 obj.TotalHarga = rdr.GetDecimal(5);
						 obj.CreatedDate = rdr.GetDateTime(6);
						 obj.CreatedBy = rdr.GetString(7);
                         if (rdr[8] != DBNull.Value) 
						    obj.ModifiedDate = rdr.GetDateTime(8);
                         if (rdr[9] != DBNull.Value) 
						    obj.ModifiedBy = rdr.GetString(9);
						 result.Add(obj);
					  }
			}
			 return result;
		}

        public List<TblTransPenjBarangDTL> GetDataAllByIdTransHDR(TblTransPenjBarangDTL tbltranspenjbarangdtl)
        {
            List<TblTransPenjBarangDTL> result = new List<TblTransPenjBarangDTL>();
            accessor = new Accessor(true, connString);
            accessor.CommandText = "TblTransPenjBarangDTL_SelectByTransHDR";
            accessor.CommandType = CommandType.StoredProcedure;
            accessor.Add(@"IdTransHDR", tbltranspenjbarangdtl.IdTransHDR);

            SqlDataReader rdr = Excutor.GetDataReader(accessor);
            if (rdr.HasRows)
            {
                while (rdr.Read())
                {
                    TblTransPenjBarangDTL obj = new TblTransPenjBarangDTL();
                    obj.IdTransDTL = rdr.GetInt32(0);
                    obj.IdTransHDR = rdr.GetInt32(1);
                    obj.IdBarang = rdr.GetInt32(2);
                    obj.Jumlah = rdr.GetInt32(3);
                    obj.HargaJual = rdr.GetDecimal(4);
                    obj.Diskon = rdr.GetInt32(5);
                    obj.TotalHarga = rdr.GetDecimal(6);
                    obj.CreatedDate = rdr.GetDateTime(7);
                    obj.CreatedBy = rdr.GetString(8);
                    obj.ModifiedDate = rdr.GetDateTime(9);
                    obj.ModifiedBy = rdr.GetString(10);

                    result.Add(obj);
                }
            }
            return result;
        }

        public SqlCommand GetCommandReportPenjualan(int IdHDR)
        {
            SqlCommand comm = new SqlCommand();
            StringBuilder s = new StringBuilder();

            s.AppendLine("SELECT");
            s.AppendLine("F.IdHDR");
            s.AppendLine(",F.TglJual");
            s.AppendLine(",D.NamaBarang");
            s.AppendLine(",A.Jumlah");
            s.AppendLine(",A.HargaJual");
            s.AppendLine(",A.Diskon");
            s.AppendLine(",A.TotalHarga");
            s.AppendLine(",E.Satuan");
            s.AppendLine("FROM TblTransPenjBarangDTL AS A");
            //s.AppendLine("TblTransPemBarangDTL AS B ON A.IdBarang = B.IdTransDTL");
            //s.AppendLine("INNER JOIN TblTransPembBarangHDR AS C ON C.IdTrans = B.IdTransHDR ");
            s.AppendLine("INNER JOIN TblBarang AS D ON D.id = A.IdBarang");
            s.AppendLine("INNER JOIN TblSatuan AS E ON E.Id = D.IdSatuan");
            s.AppendLine("INNER JOIN TblTransPenjBarangHDR AS F ON A.IdHDR = F.IdHDR");
            s.AppendLine("WHERE F.IdHDR=" + IdHDR +"");

            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;
            comm = accessor.Command;

            return comm;
        }

        public string GetIdTransHDR()
        {
            string result = string.Empty;

            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT ISNULL(MAX(CONVERT(INT,IdTransHDR)),0)+1 FROM TblTransPenjBarangDTL");

            accessor = new Accessor(true,connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;


            object i = Excutor.ExecuteScalar(accessor);
            result = i.ToString();

            return result;
        }

        public DataSet GetDataPencarianPenjualan(string tglAwal, string tglAkhir)
        {
            StringBuilder s = new StringBuilder();
            s.AppendLine("SELECT");
            s.AppendLine("A.TglJual");
            s.AppendLine(",B.NamaBarang");
            s.AppendLine(",'('+ CAST(A.Jumlah as Varchar) +')' +' '+ C.Satuan  AS Jumlah");
            s.AppendLine(",A.HargaJual AS Harga");
            s.AppendLine(",A.TotalHarga");
            s.AppendLine("FROM TblTransPenjBarangDTL A");
            s.AppendLine("INNER JOIN TblBarang B ON A.IdBarang = B.Id");
            s.AppendLine("INNER JOIN TblSatuan C ON B.IdSatuan = C.id");
            s.AppendLine("WHERE convert(varchar,A.TglJual, 103) BETWEEN '" + tglAwal + "' AND '" + tglAkhir + "'");

            DataSet result = new DataSet();
            accessor = new Accessor(true, connString);
            accessor.CommandText = s.ToString();
            accessor.CommandType = CommandType.Text;


            result = Excutor.GetDataSet(accessor);

            return result;
        }

	}
}
